package com.hspedu.com.jdbc.transaction_;

import com.hspedu.com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author DL5O
 * @version 1.0
 * 演示在JDBC 中如何控制事务
 */
public class Transaction_ {
    @Test
    public void noTransaction(){
        //操作一个转账的业务
        //1.得到连接
        Connection connection = null;
        //2.组织修改语句
        String sql = "update account set balance = balance- 100  where id = 1";
        String sql2 = "update account set balance = balance+ 100  where id = 2";
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement2 = null;
        try {
            connection = JDBCUtils.getConnection();//在默认情况下，connection对象是默认自动提交
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

            int i = 1/0;//抛出异常，后面的语句不在执行
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.executeUpdate();//执行第二条sql

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }

    //事务解决
    @Test
    public void useTransaction() throws SQLException {
        //操作一个转账的业务
        //1.得到连接
        Connection connection = null;
        //2.组织修改语句
        String sql = "update account set balance = balance- 100  where id = 1";
        String sql2 = "update account set balance = balance+ 100  where id = 2";
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement2 = null;
        try {
            connection = JDBCUtils.getConnection();//在默认情况下，connection对象是默认自动提交
            connection.setAutoCommit(false);//开启了一个事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();//执行第一条sql

            //int i = 1/0;//抛出异常，后面的语句不在执行
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.executeUpdate();//执行第二条sql
            connection.commit();//提交

        } catch (SQLException e) {
            System.out.println("执行发生了异常，撤销执行的sql！");
            connection.rollback();//回滚到事务开启的地方.
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}
